package com.googlecode.bip.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.dao.UserIgnoreQuestionDAO;
import com.googlecode.bip.dao.utils.DBHelper;
import com.googlecode.bip.dao.utils.ListableInstance;
import com.googlecode.bip.entity.IUserIgnoreQuestion;
import com.googlecode.bip.entity.impl.UserIgnoreQuestion;
import cx.ath.choisnet.sql.ConnectionQuery;

/**
 *
 * @author Claude CHOISNET
 */
public class MySQLUserIgnoreQuestionDAO
    extends MySQLDAOBuilder<IUserIgnoreQuestion>
        implements UserIgnoreQuestionDAO
{
    private static final long serialVersionUID = 2L;
    private static final Logger logger = Logger.getLogger( MySQLUserIgnoreQuestionDAO.class );

//    @Override //Listable<IQuestion>
//    public List<IUserIgnoreQuestion> getList( String sqlQuery ) throws SQLException, NamingException
//    {
//        Connection connection = MySQLDAOFactory.getConnection();
//
//        try {
//            //return DBHelper.getList( this, getDataSource(), sqlQuery );
//            return DBHelper.getList( this, connection, sqlQuery );
//            }
//        finally {
//            MySQLDAOFactory.returnConnectionToPool( connection );
//            }
//    }

    @Override //Listable<IQuestion>
    public IUserIgnoreQuestion newInstance( final ResultSet rSet )
        throws SQLException
    {
        return new UserIgnoreQuestion().set( rSet );
    }

    /**
     *
     * @param iduser
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public List<IUserIgnoreQuestion> findUserIgnoreQuestion( int iduser )
        throws SQLException, NamingException
    {
        final String sql = "SELECT * FROM `userignorequestions` WHERE `iduser`="
            + iduser
            + ";";

        return getList( sql );
    }

    /**
     *
     * @param iduser
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    public List<Integer> findIgnoreQuestionList( int iduser )
        throws SQLException, NamingException
     {
        final String sql = "SELECT * FROM `userignorequestions` WHERE `iduser`="
            + iduser
            + ";";
//        Listable<Integer> listBuilder = new Listable<Integer>()
//        {
//             @Override
//             public List<Integer> getList( String sql ) throws SQLException, NamingException
//             {
//                 Connection connection = MySQLDAOFactory.getConnection();
//    
//                 try {
//                     return DBHelper.getList( this, connection, sql );
//                     }
//                 finally {
//                     MySQLDAOFactory.returnConnectionToPool( connection );
//                     }
//             }
//             @Override
//             public Integer newInstance( ResultSet resultSet ) throws SQLException
//             {
//                 return resultSet.getInt( "idquestion" );
//             }
//        };
//
//        return listBuilder.getList( sql );
      Connection connection = MySQLDAOFactory.getConnection();
      
      try {
          return DBHelper.getList( 
                  new ListableInstance<Integer>()
                  {
                    @Override
                    public Integer newInstance( final ResultSet resultSet )
                        throws SQLException
                    {
                        return resultSet.getInt( "idquestion" );
                    }
                  }, 
                  connection, 
                  sql 
                  );
          }
       finally {
           MySQLDAOFactory.returnConnectionToPool( connection );
           }
     }

    /**
     * Insert a new IUserIgnoreQuestion (must not exist)
     * 
     * @param iquestion
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    public boolean insertUserIgnoreQuestion( final IUserIgnoreQuestion iuiq )
        throws NamingException, SQLException
    {
        final String sql = "INSERT INTO `userignorequestions` (`iduser`, `idquestion`) VALUES( "
            + iuiq.getIdUser()
            + ", "
            + iuiq.getIdQuestion()
            + " );";

        if( logger.isDebugEnabled() ) {
            logger.debug( "insertUserIgnoreQuestion: " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            int r = query.doUpdate( sql );

            return r == 1;
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    /**
     *
     * @param idquestion
     * @return
     * @throws NamingException
     * @throws SQLException
     */
    public boolean deleteUserIgnoreQuestion( final IUserIgnoreQuestion iuiq )
        throws NamingException, SQLException
    {
         final String sql = "DELETE FROM `userignorequestions` WHERE `iduser`="
            + iuiq.getIdUser()
            + " AND `idquestion`="
            + iuiq.getIdQuestion()
            + ";";

        if( logger.isDebugEnabled() ) {
            logger.debug( "deleteUserIgnoreQuestion 1: sql = " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            int x = query.doUpdate( sql );

            logger.debug( "deleteUserIgnoreQuestion 1: r = " + x );

            return x == 1;
            }
        catch( SQLException e ) {
            logger.fatal( "deleteUserIgnoreQuestion: sql = " + sql, e );
            throw e;
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    /**
     *
     * @param iduser
     * @param idquestionList
     * @throws SQLException
     * @throws NamingException
     */
    public void deleteUserIgnoreQuestion(
            final int iduser,
            final List<Integer> idquestionList
            )
        throws SQLException, NamingException
    {
        final String sql = "DELETE FROM `userignorequestions` WHERE `iduser`="
            + iduser
            + " AND `idquestion`=?;";

        if( logger.isDebugEnabled() ) {
            logger.debug( "deleteUserIgnoreQuestion 2: sql = " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();

         try {
             PreparedStatement ps = null;

             try {
                 ps = connection.prepareStatement( sql );

                 for( int idquestion : idquestionList ) {
                     ps.setInt( 1, idquestion );
                     ps.executeUpdate();
                     }
                 }
             finally {
                 if( ps != null ) {
                     ps.close();
                     }
                 }
             }
         finally {
             MySQLDAOFactory.returnConnectionToPool( connection );
             }
    }

}
